/*
 * ============================================================================
 * Copyright Notice!
 * ----------------------------------------------------------------------------
 * This file contains proprietary and confidential information belonging to the
 * group of people involvled in developing this project.  Reproduction, disclosure 
 * or use without specific written authorization from project stake holders is 
 * prohibited. Please refer the readme.txt in project installation folder for more
 * specific contacts
 *
 * All rights reserved.
 * ============================================================================
 */
package edu.uwm.universitydb.dao.professor;

import java.sql.ResultSet;
import java.sql.SQLException;

import edu.uwm.universitydb.framework.nontransactional.db.command.impl.NonTransactionalDBFetchCommandBase;
import edu.uwm.universitydb.model.Department;
import edu.uwm.universitydb.model.Professor;

/**
 * <code>SearchProfessorCommandBase</type>
 * base command class for getting a professor from data base
 * 
 * @author Shyam Nair
 */
public abstract class SearchProfessorCommandBase<T> extends NonTransactionalDBFetchCommandBase<T>
{
    /** static attribute representing column ssn */
    private static final String TUPLE_COLUMN_PROF_SSN = "p.prof_ssn";

    /** static attribute representing column name */
    private static final String TUPLE_COLUMN_PROF_NAME = "p.name";

    /** static attribute representing column age */
    private static final String TUPLE_COLUMN_PROF_AGE = "p.age";

    /** static attribute representing column rank */
    private static final String TUPLE_COLUMN_PROF_RANK = "p.rank";

    /** static attribute representing column research_speciality */
    private static final String TUPLE_COLUMN_PROF_RESEARCH_SPECIALITY =
        "p.research_speciality";

    /** static attribute representing column dept number */
    private static final String TUPLE_COLUMN_DEPT_NO = "d.dno";

    /** static attribute representing column dept name */
    private static final String TUPLE_COLUMN_DEPT_NAME = "d.name";

    /** sql */
    protected static final String SELECT_SQL = "SELECT " + TUPLE_COLUMN_PROF_SSN + ", "
        + TUPLE_COLUMN_PROF_NAME + ", " + TUPLE_COLUMN_PROF_AGE + "," + TUPLE_COLUMN_PROF_RANK
        + ", " + TUPLE_COLUMN_PROF_RESEARCH_SPECIALITY + "," + TUPLE_COLUMN_DEPT_NO + ","
        + TUPLE_COLUMN_DEPT_NAME + " FROM university.professor p, university.department d,"
        + "university.works";

    /**
     * <code>getProfessor(...)</code> parses one row of the result set to create a professor object
     * 
     * @param result
     * @return
     * @throws SQLException
     */
    protected Professor getProfessor(final ResultSet result) throws SQLException
    {
        // process one tuple
        final Integer deptNumber = result.getInt(TUPLE_COLUMN_DEPT_NO);
        final String deptName = result.getString(TUPLE_COLUMN_DEPT_NAME);
        final Department dept = new Department(deptNumber, deptName);
        final String ssn = result.getString(TUPLE_COLUMN_PROF_SSN);
        final Integer age = result.getInt(TUPLE_COLUMN_PROF_AGE);
        final String name = result.getString(TUPLE_COLUMN_PROF_NAME);
        final String rank = result.getString(TUPLE_COLUMN_PROF_RANK);
        final String researchSpeciality = result.getString(TUPLE_COLUMN_PROF_RESEARCH_SPECIALITY);
        // return the professor
        return new Professor(ssn, age, name, rank, researchSpeciality, dept);
    }
}
/*
 * ============================================================================
 * Change History
 * ----------------------------------------------------------------------------
 * Apr 19, 2014   Shyam Nair
 * File created
 * ----------------------------------------------------------------------------
 * Apr 21, 2014   Shyam Nair
 * Added Logic For Department
 * ----------------------------------------------------------------------------
 */